using System;
using System.IO;
using System.Collections.Generic;
using System.Text;
using Schema = SqlTools.SchemaReader;
using System.Data;

namespace SqlTools.CodeGenerator.Data
{
	public class DataHelper
	{
        static public void LogDataError(string anyString)
        {
            StreamWriter SW;
            SW = File.AppendText("sqlTools.log.txt");
            SW.WriteLine(anyString);
            SW.Close();

        }

		protected Project _project;

		public DataHelper(Project project)
		{
			_project = project;
		}

		public Project Project
		{
			get { return _project; }
		}

		public void CreateTables()
		{
			if ( _project.Database != null )
			{
				foreach ( Schema.Table stable in _project.Database.Tables )
				{
					if ( !_project.Tables.Contains(stable.Name) )
					{
						CreateTable(stable);
					}
				}
			}

			ResolveFKIssues(_project.Tables);
		}

		public static void ResolveFKIssues(TableCollection tables)
		{
			foreach ( Table table in tables )
			{
				FindForeignKeyMethods(table);

				ResolveReferenceProperties(table);

				if ( table.BaseTable != null )
					table.RemoveBaseTableForeignKey();
			}
		}

		public static void LoadResolveFKProperties(TableCollection tables)
		{
			foreach ( Table table in tables )
			{
				foreach ( TableProperty tp in table.Properties )
				{
					if ( tp.Type == TablePropertyType.PrimaryKey )
					{
						PrimaryKeyTableProperty pktp = tp as PrimaryKeyTableProperty;

						foreach ( TableProperty stp in pktp.Properties )
						{
							if ( stp.Type == TablePropertyType.ForeignKey )
							{
								ForeignKeyTableProperty fktp = stp as ForeignKeyTableProperty;

								fktp.LoadResolve();
							}
						}
					}
					else if ( tp.Type == TablePropertyType.ForeignKey )
					{
						ForeignKeyTableProperty fktp = tp as ForeignKeyTableProperty;

						fktp.LoadResolve();
					}
				}
			}
		}

		public static void ResolveReferenceProperties(Table table)
		{
			foreach ( TableProperty tp in table.Properties )
			{
				if ( tp.Type == TablePropertyType.PrimaryKey )
				{
					PrimaryKeyTableProperty pktp = tp as PrimaryKeyTableProperty;

					foreach ( TableProperty stp in pktp.Properties )
					{
						if ( stp.Type == TablePropertyType.ForeignKey )
						{
							ForeignKeyTableProperty fktp = stp as ForeignKeyTableProperty;

							ResolveForeignKeyReferenceProperty(fktp);
						}
					}
				}
				else if ( tp.Type == TablePropertyType.ForeignKey )
				{
					ForeignKeyTableProperty fktp = tp as ForeignKeyTableProperty;

					ResolveForeignKeyReferenceProperty(fktp);
				}
			}
		}

		public static void ResolveTableIssues(TableCollection tables)
		{
			foreach ( Table table in tables )
			{
				ResolveBaseTable(table);
			}
		}

		public static void ResolveBaseTable(Table table)
		{
			table.LoadResolve();
		}

		public static void FindForeignKeyMethods(Table table)
		{
			foreach ( TableProperty tp in table.Properties )
			{
				if ( tp.Type == TablePropertyType.PrimaryKey )
				{
					PrimaryKeyTableProperty pktp = tp as PrimaryKeyTableProperty;

					foreach ( TableProperty stp in pktp.Properties )
					{
						if ( stp.Type == TablePropertyType.ForeignKey )
						{
							ForeignKeyTableProperty fktp = stp as ForeignKeyTableProperty;

							fktp.ForeignRetrieve = FindMethod(fktp.ForeignKeyTable, fktp.ForeignKeyColumnName);
						}
					}
				}
				else if ( tp.Type == TablePropertyType.ForeignKey )
				{
					ForeignKeyTableProperty fktp = tp as ForeignKeyTableProperty;

					fktp.ForeignRetrieve = FindMethod(fktp.ForeignKeyTable, fktp.ForeignKeyColumnName);
				}
			}
		}

		public Table CreateTable(Schema.Table sTable)
		{
            //TODOS GPC remove troublesome spaces in class/method names
			Table table = new Table(_project, sTable.Name);
			_project.Tables.Add(table);

			PrimaryKeyTableProperty pkprop = new PrimaryKeyTableProperty("PK", table, "");
			table.Properties.Add(pkprop);

			table.BusinessObjectName = _project.CreateTableObjectName(sTable.Name);
            table.BusinessObjectName = table.BusinessObjectName.Replace(' ', '_');

			table.PrimaryKeyClassName = table.BusinessObjectName + "PrimaryKey";
            table.PrimaryKeyClassName = table.PrimaryKeyClassName.Replace(' ', '_');

			table.PrimaryKeyFieldName = NamingHelper.MemberVariableName(table.PrimaryKeyClassName);
            table.PrimaryKeyFieldName = table.PrimaryKeyFieldName.Replace(' ', '_');

			table.DbObjectName = "Db" + table.BusinessObjectName;
            table.DbObjectName = table.DbObjectName.Replace(' ', '_');

			table.BusinessDirectory = _project.DefaultBusinessDirectory;
			table.BusinessNamespace = _project.DefaultBusinessNamespace;
			table.DataDirectory = _project.DefaultDataDirectory;
			table.DataNamespace = _project.DefaultDataNamespace;
			table.SQLDirectory = _project.DefaultSQLDirectory;

			// create select by pk method
			Method selectByPk = new Method("SelectByPK", table, MethodType.SelectSingle);
            selectByPk.MethodName = selectByPk.MethodName.Replace(' ', '_');

			selectByPk.StoredProcedureName = _project.CreateStoredProcedureName(selectByPk);
            selectByPk.StoredProcedureName = selectByPk.StoredProcedureName.Replace(' ', '_');

			selectByPk.Parameters.Add(new MethodParameter("key", selectByPk, pkprop));
			table.Methods.Add(selectByPk);

			table.SelectByPK = selectByPk;

			Method insert = new Method("Insert", table, MethodType.Insert);
			insert.StoredProcedureName = _project.CreateStoredProcedureName(insert);
			table.Methods.Add(insert);

			Method update = new Method("Update", table, MethodType.Update);
			update.StoredProcedureName = _project.CreateStoredProcedureName(update);
			table.Methods.Add(update);

			Method delete = new Method("Delete", table, MethodType.Delete);
			delete.StoredProcedureName = _project.CreateStoredProcedureName(delete);
			table.Methods.Add(delete);

			foreach ( Schema.Column sColumn in sTable.Columns )
			{
				if ( sColumn.IsPrimaryKey )
				{
					if ( sColumn.IsForeignKey )
					{
						ForeignKeyTableProperty fkp = CreateForeignKeyProperty(table, sColumn, sTable);

						pkprop.Properties.Add(fkp);
					}
					else
					{
						IntrinsicTableProperty itp = CreateIntrinsicProperty(table, sColumn);

						pkprop.Properties.Add(itp);
					}
				}
				else
				{
					if ( sColumn.IsForeignKey )
					{
						ForeignKeyTableProperty fkp = CreateForeignKeyProperty(table, sColumn, sTable);

						table.Properties.Add(fkp);
					}
					else
					{
						IntrinsicTableProperty itp = CreateIntrinsicProperty(table, sColumn);

						table.Properties.Add(itp);
					}
				}
			}

			//foreach ( Schema.ForeignKey fk in sTable.ForeignKeys )
			//{
			//    if ( fk.ReferenceTable == sTable )
			//    {
			//        ForeignKeyTableProperty fktp = CreateForeignKeyProperty(table, fk.ReferenceColumn, sTable, fk);
			//        table.Properties.Add(fktp);
			//    }
			//}

			return table;
		}

		private static Method FindMethod(Table ftable, string columnName)
		{
			return FindMethod(ftable, columnName, false);
		}

		private static Method FindMethod(Table ftable, string columnName, bool isSingle)
		{
            //TODOS GPC
            if(ftable==null)
                return new Method("", ftable, MethodType.Custom);
            if(ftable.Methods==null)
                return new Method("", ftable, MethodType.Custom);
            if (ftable.Methods.Count == 0)
                return new Method("", ftable,MethodType.Custom);

			Method[] methods = ftable.Methods.GetMethodsByType(MethodType.SelectMultiple);

			Method retreive = null;

			foreach ( Method method in methods )
			{
				if ( method.Parameters.Count == 1 &&
					( method.Parameters[0].Property.Table == ftable &&
					  method.Parameters[0].Property.ColumnName == columnName )
					)
				{
					retreive = method;
					break;
				}
			}

			if ( retreive == null )
			{
				// find the property
				TableProperty methProp = null;

				foreach ( TableProperty prop in ftable.Properties )
				{
					if ( prop.Type == TablePropertyType.PrimaryKey )
					{
						PrimaryKeyTableProperty pkprop = prop as PrimaryKeyTableProperty;

						foreach ( TableProperty pksubprop in pkprop.Properties )
						{
							if ( pksubprop.Type != TablePropertyType.ForeignKey )
								continue;

							ForeignKeyTableProperty fksubprop = pksubprop as ForeignKeyTableProperty;

							if ( fksubprop.ForeignKeyColumnName == columnName )
							{
								methProp = fksubprop;
								break;
							}
						}
					}
					else
					{
						if ( prop.Type != TablePropertyType.ForeignKey )
						{
							if ( prop.ColumnName == columnName )
							{
								methProp = prop;
								break;
							}
						}
						else
						{
							ForeignKeyTableProperty fksubprop = prop as ForeignKeyTableProperty;

							if ( fksubprop.ForeignKeyColumnName == columnName )
							{
								methProp = fksubprop;
								break;
							}
						}
					}

					if ( methProp != null )
						break;
				}

				string methName = "SelectBy";

				if ( methProp != null )
					methName += methProp.PropertyName;
				else
					methName += columnName;

				retreive = new Method(methName, ftable, ( isSingle ? MethodType.SelectSingle : MethodType.SelectMultiple ));
				retreive.StoredProcedureName = ftable.Project.CreateStoredProcedureName(retreive);

				retreive.Parameters.Add(new MethodParameter(NamingHelper.ClassVariableName(columnName), retreive, methProp));

				ftable.Methods.Add(retreive);
			}

			return retreive;

		}

		public static void ResolveForeignKeyReferenceProperty(ForeignKeyTableProperty fkprop)
		{
            //TODOS GPC
            if (fkprop == null) return;
            if (fkprop.ReferenceTable == null) return;
            if (fkprop.ReferenceTable.Properties == null) return;
			foreach ( TableProperty tp in fkprop.ReferenceTable.Properties )
			{
				if ( tp.Type == TablePropertyType.PrimaryKey )
				{
					PrimaryKeyTableProperty pktp = tp as PrimaryKeyTableProperty;

					foreach ( TableProperty stp in pktp.Properties )
					{
						if ( stp.ColumnName == fkprop.ReferenceColumnName && ( stp.Type == TablePropertyType.Intrinsic || stp.Type == TablePropertyType.ForeignKey ) )
						{
							// found it
							if ( stp.ForeignKeys.Contains(fkprop) )
								continue;

							// check for name collisions on the target prop
							int namecount = 0;

							foreach ( ForeignKeyTableProperty fktp in stp.ForeignKeys )
							{
								if ( fktp.ForeignObjectFieldName == fkprop.ForeignObjectFieldName || fktp.ForeignObjectPropertyName == fkprop.ForeignObjectPropertyName )
								{
									namecount++;
									break;
								}
							}

							if ( namecount > 0 )
							{
								fkprop.ForeignObjectPropertyName += ( namecount + 1 ).ToString();
								fkprop.ForeignObjectFieldName += ( namecount + 1 ).ToString();
							}

							stp.ForeignKeys.Add(fkprop);

							return;
						}
					}
				}
				else
				{
					if ( tp.ColumnName == fkprop.ReferenceColumnName && ( tp.Type == TablePropertyType.Intrinsic || tp.Type == TablePropertyType.ForeignKey ) )
					{
						// found it
						if ( tp.ForeignKeys.Contains(fkprop) )
							continue;

						// check for name collisions on the target prop
						int namecount = 0;

						foreach ( ForeignKeyTableProperty fktp in tp.ForeignKeys )
						{
							if ( fktp.ForeignObjectFieldName == fkprop.ForeignObjectFieldName || fktp.ForeignObjectPropertyName == fkprop.ForeignObjectPropertyName )
							{
								namecount++;
								break;
							}
						}

						if ( namecount > 0 )
						{
							fkprop.ForeignObjectPropertyName += ( namecount + 1 ).ToString();
							fkprop.ForeignObjectFieldName += ( namecount + 1 ).ToString();
						}

						tp.ForeignKeys.Add(fkprop);


						return;
					}
				}
			}
		}

		private ForeignKeyTableProperty CreateForeignKeyProperty(Table table, Schema.Column sColumn, Schema.Table sTable)
		{
			Schema.ForeignKey sFK = null;
			foreach ( Schema.ForeignKey fk in sTable.ForeignKeys )
			{
				if ( fk.ForeignKeyColumn == sColumn )
				{
					sFK = fk;
					break;
				}
			}

			return CreateForeignKeyProperty(table, sColumn, sTable, sFK);
		}

		private ForeignKeyTableProperty CreateForeignKeyProperty(Table table, Schema.Column sColumn, Schema.Table sTable, Schema.ForeignKey sFK)
		{
            ForeignKeyTableProperty fkprop = null;
            try
            {
                fkprop = new ForeignKeyTableProperty(sColumn.Name, table, sColumn.Name);
                FillProperty(fkprop, table, sColumn);

                Table rtable = _project.Tables[sFK.ReferenceTable.Name];

                if (rtable == null)
                {
                    rtable = CreateTable(sFK.ReferenceTable);
                }


                fkprop.ForeignKeyTable = table;
                fkprop.ForeignKeyColumnName = sColumn.Name;
                fkprop.ForeignObjectFieldName = NamingHelper.PluralMemberVariableName(table.BusinessObjectName);
                fkprop.ForeignObjectPropertyName = NamingHelper.TitleCase(NamingHelper.Pluralize(table.BusinessObjectName));
                fkprop.ForeignFieldName = NamingHelper.MemberVariableName(sFK.ReferenceColumn.Name);
                fkprop.ForeignPropertyName = sFK.ReferenceColumn.Name;

                fkprop.ReferenceTable = rtable;
                fkprop.ReferenceColumnName = sFK.ReferenceColumn.Name;
                fkprop.ReferenceObjectFieldName = NamingHelper.MemberVariableName(NamingHelper.RemoveID(sColumn.Name));
                fkprop.ReferenceObjectPropertyName = NamingHelper.TitleCase(NamingHelper.RemoveID(sColumn.Name));
                fkprop.ReferenceFieldName = NamingHelper.MemberVariableName(sFK.ForeignKeyColumn.Name);
                fkprop.ReferencePropertyName = NamingHelper.TitleCase(sFK.ForeignKeyColumn.Name);

                // check to see that reference object and reference value names are different

                if (fkprop.ReferenceObjectFieldName == fkprop.ReferenceFieldName)
                {
                    fkprop.ReferenceFieldName += "_Value";
                }

                if (fkprop.ReferenceObjectPropertyName == fkprop.ReferencePropertyName)
                {
                    fkprop.ReferencePropertyName += "_Value";
                }

                bool isRefPk = false;

                PrimaryKeyTableProperty refPK = rtable.GetPrimaryKey();

                if (refPK != null)
                {
                    TableProperty ttp = refPK.Properties.FindPropertyByColumnName(fkprop.ReferenceColumnName);

                    isRefPk = (ttp != null);
                }

                if (isRefPk)
                {
                    fkprop.ReferenceRetrieve = rtable.SelectByPK;
                }
                else
                {
                    fkprop.ReferenceRetrieve = FindMethod(rtable, fkprop.ReferenceColumnName, true);
                }

                return fkprop;
            }
            catch (Exception Exp)
            {
                //TODOS GPC
                DataHelper.LogDataError("Problem with foreign key " + table.TableName + " " + Exp.Message.ToString());
               // TODOS GPC              
               return fkprop;
               
              // throw new Exception("Problem with foreign key " + table.TableName + " " + Exp.Message.ToString());
            }

		}

		protected IntrinsicTableProperty CreateIntrinsicProperty(Table table, Schema.Column sColumn)
		{
			IntrinsicTableProperty itp = new IntrinsicTableProperty(sColumn.Name, table, sColumn.Name);

			FillProperty(itp, table, sColumn);

			return itp;
		}

		protected void FillProperty(TableProperty itp, Table table, Schema.Column sColumn)
		{
			if ( sColumn.Default != null || sColumn.IsIdentity || sColumn.IsComputed )
				itp.AutoGeneratedOnInsert = true;

			itp.Nullable = sColumn.IsNullable;

			itp.FieldName = NamingHelper.MemberVariableName(sColumn.Name);
			itp.PropertyName = NamingHelper.TitleCase(sColumn.Name);
			itp.DataType = GetSqlDbTypeFromString(sColumn.DataType);
			if ( itp.CSharpDataType == "string" )
				itp.DataLength = sColumn.Length;
		}

		public SqlDbType GetSqlDbTypeFromString(string type)
		{
			switch ( type.ToLower() )
			{
				case "bigint":
					return SqlDbType.BigInt;
				case "binary":
					return SqlDbType.Binary;
				case "bit":
					return SqlDbType.Bit;
				case "char":
					return SqlDbType.Char;
				case "datetime":
					return SqlDbType.DateTime;
				case "decimal":
					return SqlDbType.Decimal;
				case "float":
					return SqlDbType.Float;
				case "image":
					return SqlDbType.Image;
				case "int":
					return SqlDbType.Int;
				case "money":
					return SqlDbType.Money;
				case "nchar":
					return SqlDbType.NChar;
				case "ntext":
					return SqlDbType.NText;
				case "numeric":
					return SqlDbType.Decimal;
				case "nvarchar":
					return SqlDbType.NVarChar;
				case "real":
					return SqlDbType.Real;
				case "smalldatetime":
					return SqlDbType.SmallDateTime;
				case "smallint":
					return SqlDbType.SmallInt;
				case "smallmoney":
					return SqlDbType.SmallMoney;
				case "sql_variant":
					return SqlDbType.Variant;
				case "text":
					return SqlDbType.Text;
				case "timestamp":
					return SqlDbType.Timestamp;
				case "tinyint":
					return SqlDbType.TinyInt;
				case "uniqueidentifier":
					return SqlDbType.UniqueIdentifier;
				case "varbinary":
					return SqlDbType.VarBinary;
				case "varchar":
					return SqlDbType.VarChar;
				case "xml":
					return SqlDbType.Xml;
			}

			return SqlDbType.Int;
		}
	}
}
